package com.SNQ.project.English_speaking_learning_system.mapper;

import com.SNQ.project.English_speaking_learning_system.aspect.AutoFill;
import com.SNQ.project.English_speaking_learning_system.common.enumeration.OperationType;
import com.SNQ.project.English_speaking_learning_system.pojo.entity.User;
import com.SNQ.project.English_speaking_learning_system.pojo.vo.UserVO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {

    /**
     * 根据用户名查询用户
     * @param username
     * @return
     */
    @Select("select * from user where username = #{username}")
    User getByUsername(String username);

    /**
     * 插入用户数据
     * @param user
     */
    @Insert("insert into user (name, username, password, phone, sex, id_number, class_number, department, grade, major, checkin_count, scores, create_time) " +
            "values " +
            "(#{name},#{username},#{password},#{phone},#{sex},#{idNumber},#{classNumber},#{department},#{grade},#{major},#{checkinCount},#{scores},#{createTime})")
    @AutoFill(value = OperationType.INSERT)
    void insert(User user);

    @Select("SELECT id, username, checkin_count, scores " +
            "FROM user " +
            "ORDER BY checkin_count DESC " +
            "LIMIT 10")
    List<UserVO> getArticleByCheckin();

    @Select("SELECT id, username, checkin_count, scores " +
            "FROM user " +
            "ORDER BY scores DESC " +
            "LIMIT 10")
    List<UserVO> getArticleByScores();

    @Select("SELECT id, username, checkin_count, scores " +
            "FROM user " +
            "WHERE department = #{department} " + // 根据传入的院系过滤用户
            "ORDER BY checkin_count DESC " + // 按打卡次数降序排列
            "LIMIT 10")
    List<UserVO> getDepartmentByCheckin(String department);

    @Select("SELECT id, username, checkin_count, scores " +
            "FROM user " +
            "WHERE department = #{department} " + // 根据传入的院系过滤用户
            "ORDER BY scores DESC " + // 按打卡次数降序排列
            "LIMIT 10")
    List<UserVO> getDepartmentByScores(String department);

    @Select("SELECT id, username, checkin_count, scores " +
            "FROM user " +
            "WHERE department = #{department} AND major = #{major} " + // 根据传入的院系过滤用户
            "ORDER BY checkin_count DESC " + // 按打卡次数降序排列
            "LIMIT 10")
    List<UserVO> getMajorByCheckin(String department,String major);

    @Select("SELECT id, username, checkin_count, scores " +
            "FROM user " +
            "WHERE department = #{department} AND major = #{major} " + // 根据传入的院系过滤用户
            "ORDER BY scores DESC " + // 按打卡次数降序排列
            "LIMIT 10")
    List<UserVO> getMajorByScores(String department,String major);

    @Select("SELECT id, username, checkin_count, scores " +
            "FROM user " +
            "WHERE department = #{department} AND major = #{major} AND class_number = #{classNumber} " + // 根据传入的院系过滤用户
            "ORDER BY checkin_count DESC " + // 按打卡次数降序排列
            "LIMIT 10")
    List<UserVO> getClassNumberByCheckin(String department,String major,String classNumber);

    @Select("SELECT id, username, checkin_count, scores " +
            "FROM user " +
            "WHERE department = #{department} AND major = #{major} AND class_number = #{classNumber} " + // 根据传入的院系过滤用户
            "ORDER BY scores DESC " + // 按打卡次数降序排列
            "LIMIT 10")
    List<UserVO> getClassNumberByScores(String department,String major,String classNumber);


}
